using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using Krystalware.SlickUpload;
using Krystalware.SlickUpload.Configuration;
using Krystalware.SlickUpload.Storage;
using Krystalware.SlickUpload.Storage.Streams;

namespace $Namespace.Storage
{
    /// <summary>Provides a simple repository that access the file data in SQL.</summary>
    /// <remarks>When implementing access in your own project, we highly recommended to reimplement
    ///          this functionality using whatever existing data access pattern
    ///          you are using in your project.
    /// </remarks>
    public class SqlFileRepository
    {
        string _cnString;
        string _table;
        string _keyField;
        string _nameField;
        string _dataField;
        SqlColumnDataType _dataType;
    
        public SqlFileRepository(string uploadProfile)
        {
            UploadStreamProviderElement section = SlickUploadContext.Config.UploadProfiles[uploadProfile].UploadStreamProvider;

            string connectionStringName = section.Parameters["connectionStringName"];

            if (!string.IsNullOrEmpty(connectionStringName))
                _cnString = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
            else
                _cnString = section.Parameters["connectionString"];

            _table = section.Parameters["table"];
            _keyField = section.Parameters["keyField"];
            _nameField = section.Parameters["fileNameField"];
            _dataField = section.Parameters["dataField"];

            string dataTypeString = section.Parameters["dataType"];

            if (!string.IsNullOrEmpty(dataTypeString))
                _dataType = (SqlColumnDataType)Enum.Parse(typeof(SqlColumnDataType), dataTypeString, true);
            else
                _dataType = SqlColumnDataType.Image;
        }
        
        public Stream GetDataStream(SqlFile file)
        {
            if (_dataType != SqlColumnDataType.FileStream)
                return new SqlClientReadStream(_cnString, _table, _dataField, file.BuildCriteria, _dataType);
            else
                return new SqlFileStreamReadStream(_cnString, _table, _dataField, file.BuildCriteria);
        }

        public List<SqlFile> GetAll()
        {        
            return GetAll(false);
        }

        public List<SqlFile> GetAll(bool hasCategory)
        {        
            List<SqlFile> files = new List<SqlFile>();

            using (IDbConnection cn = new SqlConnection(_cnString))
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT " + _keyField + ", " + _nameField + ", CAST(DATALENGTH(" + _dataField + ") AS bigint) AS Length";
                
                if (hasCategory)
                    cmd.CommandText += ", Category";

                cmd.CommandText += " FROM " + _table;

                cn.Open();

                using (IDataReader rd = cmd.ExecuteReader(CommandBehavior.SingleResult))
                {
                    while (rd.Read())
                    {
                        if (hasCategory)
                            files.Add(new SqlFile(Convert.ToInt64(rd.GetValue(0)), rd.GetString(1), rd.GetInt64(2), _keyField, rd.GetString(3)));
                        else
                            files.Add(new SqlFile(Convert.ToInt64(rd.GetValue(0)), rd.GetString(1), rd.GetInt64(2), _keyField));
                    }
                }
            }

            return files;
        }

        public SqlFile GetById(long id)
        {
            using (IDbConnection cn = new SqlConnection(_cnString))
            using (IDbCommand cmd = cn.CreateCommand())
            {
                cmd.CommandText = "SELECT " + _nameField + ", CAST(DATALENGTH(" + _dataField + ") AS bigint) AS Length FROM " + _table + " WHERE " + _keyField + "=" + id.ToString();

                cn.Open();

                using (IDataReader rd = cmd.ExecuteReader(CommandBehavior.SingleRow))
                {
                    if (rd.Read())
                    {
                        return new SqlFile(id, rd.GetString(0), rd.GetInt64(1), _keyField);
                    }
                    else
                    {
                        return null;
                    }
                }
            }
        }
    }
}